﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data.Common;
using System.Data.SQLite;
using LumenWorks.Framework.IO.Csv;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Data;

namespace Interface
{
	class MainClass
	{
	    List<string> years;
         
        public void database()
        {
            SQLiteConnection connect = new SQLiteConnection();
            connect.ConnectionString = "Data Source=rebides.db";
            connect.Open();
       
            //database transaction
            SQLiteTransaction transaction = connect.BeginTransaction();

            //command
            SQLiteCommand cmd = connect.CreateCommand();

            cmd.CommandText = "DROP TABLE IF EXISTS rebides";
            cmd.ExecuteNonQuery();

            cmd.CommandText = "CREATE TABLE IF NOT EXISTS rebides (cod_docente varchar," +
                                                        "nome varchar," +
                                                        "ultimo_Grau varchar," +
                                                        "curso varchar," +
                                                        "categoria varchar," +
                                                        "regime_tempo varchar," +
                                                        "ano_Lectivo varchar," +
                                                        "tipo_estabelecimento varchar," +
                                                        "nome_estabelecimento varchar)";

            cmd.ExecuteNonQuery(); 

            for (int num = 0; num <= 10; num++)
            {
                CsvReader csvreader = new CsvReader(new StreamReader("rebcsv/reb" + num + ".csv"), true);
                while (csvreader.ReadNextRecord())
                {
                    cmd.CommandText = string.Format("INSERT INTO rebides VALUES('{0}','{1}','{2}','{3}','{4}'," +
                                                        "'{5}','{6}','{7}','{8}')", csvreader[0].Replace("\'", ""),
                                                        csvreader[1].Replace("\'", ""), csvreader[2].Replace("\'", ""),
                                                        csvreader[3].Replace("\'", ""), csvreader[4].Replace("\'", ""),
                                                        csvreader[5].Replace("\'", ""), csvreader[6].Replace("\'", ""),
                                                        csvreader[7].Replace("\'", ""), csvreader[8].Replace("\'", ""));
                    cmd.ExecuteNonQuery();
                }
            }

            transaction.Commit();
            connect.Close();

            DialogResult n = MessageBox.Show("Base de Dados completada com sucesso!", "Concluido!", MessageBoxButtons.OK);
        }

        public List<string> getListOfYears()
        {
            years = new List<string>();
            SQLiteConnection connect = new SQLiteConnection();
            connect.ConnectionString = "Data Source=rebides.db;FailIfMissing=False;";
            connect.Open();

            SQLiteCommand cmd = connect.CreateCommand();
            cmd.Connection = connect;

            cmd.CommandText = "SELECT distinct ano_lectivo FROM rebides";

            SQLiteDataReader csvreader;

            csvreader = cmd.ExecuteReader();
            while (csvreader.Read())
            {
                years.Add((string)csvreader[0]);
            }

            years.Sort();

            connect.Close();
            return years;
        }

        public List<Query1> Query1(string year)
        {
            //To create a list with objects from statistic 1
            List<Query1> obj1 = new List<Query1>();

            SQLiteConnection connect = new SQLiteConnection("Data Source=rebides.db;FailIfMissing=False;");
            connect.Open();

            SQLiteTransaction transaction = connect.BeginTransaction();

            SQLiteCommand cmd = connect.CreateCommand();

            //all the information from database is selected
            cmd.CommandText = "SELECT * FROM rebides";
            cmd.CommandType = CommandType.Text;

            //to save the result from the query
            DbDataReader csvreader = cmd.ExecuteReader();
       
            int counter = 0;
            while (csvreader.Read())
            {
                if ((string)csvreader[6] == year)
                {
                    string establishment = (string)csvreader[8];
                    counter++;
                }
            }

                obj1.Add(new Query1
                {
                    Years = year.ToString(),
                    Establishments = counter
                });
                              
            transaction.Commit();
            connect.Close();

            return (obj1);
        }

        public List<Query2> Query2()
        {
            //To create a list with objects from statistic 2
            List<Query2> obj2 = new List<Query2>();
         
            SQLiteConnection connect = new SQLiteConnection("Data Source=rebides.db;FailIfMissing=False;");
            connect.Open();

            SQLiteTransaction transaction = connect.BeginTransaction();
            SQLiteCommand cmd = connect.CreateCommand();

            cmd.CommandText = "SELECT * FROM rebides";
            cmd.CommandType = CommandType.Text;

            DbDataReader csvreader = cmd.ExecuteReader();

            while (csvreader.Read())
            {
                obj2.Add(new Query2
                {
                    Years = (string)csvreader[6],
                    Categories = (string)csvreader[4]
                });
            }

            //using LINQ for the statistics 
            var item = from data in obj2
                        group data by new { data.Years, data.Categories } into orderlist
                        select new
                        {
                            orderlist.Key.Years,
                            orderlist.Key.Categories,
                            count = orderlist.Count()

                        };

            List<Query2> Query2Object = new List<Query2>();
            
            //the result from LINQ query is returned to an object list 
            foreach (var data in item)
            {
                Query2Object.Add(new Query2
                {
                    Years = data.Years.ToString(),
                    Categories = data.Categories.ToString(),
                    Num_Teachers = data.count
                });
            }

            transaction.Commit();
            connect.Close();

            return Query2Object;
        }

        public List<Query3> Query3()
        {
            List<Query3> obj3 = new List<Query3>();

            SQLiteConnection connect = new SQLiteConnection("Data Source=rebides.db;FailIfMissing=False;");
            connect.Open();

            SQLiteTransaction transaction = connect.BeginTransaction();

            SQLiteCommand cmd = connect.CreateCommand();

            cmd.CommandText = "SELECT * FROM rebides";
            cmd.CommandType = CommandType.Text;

            //to save the result from the query
            DbDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                obj3.Add(new Query3
                {
                    Establishments = (string)reader[8],
                    Categories = (string)reader[4]
                });
            }

            //using LINQ for the statistics
            var item = from data in obj3
                        group data by new { data.Establishments, data.Categories } into orderlist
                        select new
                        {
                            orderlist.Key.Establishments,
                            orderlist.Key.Categories,
                            count = orderlist.Count()
                        };

            List<Query3> Query3Obj = new List<Query3>();

            //the result from LINQ query is returned to an object list
            foreach (var data in item)
            {
                Query3Obj.Add(new Query3
                {
                    Establishments = data.Establishments.ToString(),
                    Categories = data.Categories.ToString(),
                    Num_Teachers = data.count
                });
            }

            transaction.Commit();
            connect.Close();

            return Query3Obj;
        
        }

    }
}
